-- drop function if exists sm_sc.fv_mx_ele_3d_2_2d(anyarray, int[2], int);
create or replace function sm_sc.fv_mx_ele_3d_2_2d
(
  i_array_3d        anyarray,  
  i_dims_from_to    int[2]  ,  -- 合并维度的原来两个维度。合并后的新维度在 to 的顺序位置。当 from 与 to 为相邻维度时，[from, to] 等价于 [to, from]。
                               -- 枚举项包括，[1, 2] === [2, 1]; [2, 3] === [3, 2]; [1, 3]; [3, 1]
  i_dim_pin_ele     int        -- 被定住元素顺序的旧维度。该旧维度下的元素顺序，将保留至新维度。i_dim_pin_ele 为 from 或 to，不能为其他值。
)
returns anyarray
as
$$
-- declare

begin
  if array_ndims(i_array_3d) <> 3
  then 
    raise exception 'ndims should be 3.';
  end if;
  
  if i_dim_pin_ele <> all(i_dims_from_to)
  then 
    raise exception 'the pin_ele dim must be one of from_to dims.';
  end if;
  
  if i_dims_from_to not in (array[1, 2], array[2, 1], array[2, 3], array[3, 2], array[1, 3], array[3, 1])
  then 
    raise exception 'unsupport other i_dims_from_to except [1, 2] === [2, 1]; [2, 3] === [3, 2]; [1, 3]; [3, 1].';
  end if;

  if i_array_3d is null 
  then 
    return null;
  elsif i_dims_from_to in (array[1, 2], array[2, 1])
  then
    if i_dim_pin_ele = 1
    then 
      return 
      (
        with 
        cte_slice_x as 
        (
          select 
            a_cur_y,
            a_cur_z,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_x) as a_slice_x
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_y, a_cur_z
        )
        select 
          array_agg(a_slice_x order by a_cur_y, a_cur_z)
        from cte_slice_x
      );    
    else  -- -- elsif i_dim_pin_ele = 2
    -- -- then
      return 
      (
        with 
        cte_slice_x as 
        (
          select 
            a_cur_y,
            a_cur_z,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_x) as a_slice_x
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_y, a_cur_z
        )
        select 
          array_agg(a_slice_x order by a_cur_z, a_cur_y)
        from cte_slice_x
      );
    end if;
  elsif i_dims_from_to = array[1, 3]
  then
    if i_dim_pin_ele = 1
    then
      return 
      (
        with 
        cte_slice_z as 
        (
          select 
            a_cur_y,
            a_cur_x,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_z) as a_slice_z
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_y, a_cur_x
        ),
        cte_slice_x_z as 
        (
          select 
            a_cur_y, 
            sm_sc.fa_array_concat(a_slice_z order by a_cur_x) as a_slice_x_z
          from cte_slice_z
          group by a_cur_y
        )
        select 
          array_agg(a_slice_x_z order by a_cur_y)
        from cte_slice_x_z        
      );
    else  -- -- elsif i_dim_pin_ele = 3
    -- -- then
      return 
      (
        with 
        cte_slice_x as 
        (
          select 
            a_cur_y,
            a_cur_z,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_x) as a_slice_x
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_y, a_cur_z
        ),
        cte_slice_z_x as 
        (
          select 
            a_cur_y, 
            sm_sc.fa_array_concat(a_slice_x order by a_cur_z) as a_slice_z_x
          from cte_slice_x
          group by a_cur_y
        )
        select 
          array_agg(a_slice_z_x order by a_cur_y)
        from cte_slice_z_x
      );
    end if;
  elsif i_dims_from_to = array[3, 1]
  then
    if i_dim_pin_ele = 1
    then
      return 
      (
        with 
        cte_slice_y as 
        (
          select 
            a_cur_z,
            a_cur_x,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_y) as a_slice_y
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_z, a_cur_x
        )
        select 
          array_agg(a_slice_y order by a_cur_x, a_cur_z)
        from cte_slice_y
      );
    else  -- -- elsif i_dim_pin_ele = 3
    -- -- then
      return 
      (
        with 
        cte_slice_y as 
        (
          select 
            a_cur_z,
            a_cur_x,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_y) as a_slice_y
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_z, a_cur_x
        )
        select 
          array_agg(a_slice_y order by a_cur_z, a_cur_x)
        from cte_slice_y
      );
    end if;
  elsif i_dims_from_to in (array[2, 3], array[3, 2])
  then
    if i_dim_pin_ele = 2
    then 
      return 
      (
        with 
        cte_slice_y as 
        (
          select 
            a_cur_z,
            a_cur_x,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_y) as a_slice_y
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_z, a_cur_x
        ),
        cte_slice_x_y as 
        (
          select 
            a_cur_z, 
            sm_sc.fa_array_concat(a_slice_y order by a_cur_x) as a_slice_x_y
          from cte_slice_y
          group by a_cur_z
        )
        select 
          array_agg(a_slice_x_y order by a_cur_z)
        from cte_slice_x_y
      );
    else  -- -- elsif i_dim_pin_ele = 3
    -- -- then
      return 
      (
        with 
        cte_slice_x as 
        (
          select 
            a_cur_z,
            a_cur_y,
            array_agg(i_array_3d[a_cur_z][a_cur_y][a_cur_x] order by a_cur_x) as a_slice_x
          from generate_series(1, array_length(i_array_3d, 1)) tb_a_cur_z(a_cur_z)
            , generate_series(1, array_length(i_array_3d, 2)) tb_a_cur_y(a_cur_y)
            , generate_series(1, array_length(i_array_3d, 3)) tb_a_cur_x(a_cur_x)
          group by a_cur_z, a_cur_y
        ),
        cte_slice_y_x as 
        (
          select 
            a_cur_z, 
            sm_sc.fa_array_concat(a_slice_x order by a_cur_y) as a_slice_y_x
          from cte_slice_x
          group by a_cur_z
        )
        select 
          array_agg(a_slice_y_x order by a_cur_z)
        from cte_slice_y_x
      );
    end if;
  end if;
end
$$
language plpgsql stable
parallel safe
cost 100;
-- -- set search_path to sm_sc;
-- with cte_arr as
-- (
--   select 
--     array
--     [
--       [
--         [1, 2, 3, 4],
--         [5, 6, 7, 8],
--         [9, 10, 11, 12]
--       ],
--       [
--         [21, 22, 23, 24],
--         [25, 26, 27, 28],
--         [29, 30, 31, 32]
--       ]
--     ] as a_arr
-- )
-- select 
--   a_dims_from_to, a_dim_pin_ele,
--   sm_sc.fv_mx_ele_3d_2_2d(a_arr, a_dims_from_to, a_dim_pin_ele) as a_out
-- from cte_arr
--   , (select array[1, 2] union all select array[1, 3] union all select array[3, 1] union all select array[2, 3]) tb_a_dims_from_to(a_dims_from_to)
--   , generate_series(1, 3) tb_a_dim_pin_ele(a_dim_pin_ele)
-- where a_dim_pin_ele = any(a_dims_from_to)
-- order by least(|@<| a_dims_from_to, |@>| a_dims_from_to)
--   , a_dims_from_to[1]
--   , greatest(|@<| a_dims_from_to, |@>| a_dims_from_to)
--   , a_dim_pin_ele